import time

import mysql.connector
import xlrd


def initMysql():
  global mydb
  global mycursor
  mydb = mysql.connector.connect(
    host='8.129.219.66',
    user='guifangtong',
    passwd='guifangtong1234!@#',
    database='guifangtong'
  )

  # mydb = mysql.connector.connect(
  #   host='localhost',
  #   user='root',
  #   passwd='root1234',
  #   database='guifangtong'
  # )
  mycursor = mydb.cursor()


def readExcel(f):
  bok = xlrd.open_workbook(f)
  return bok


global cunz
cunz = 0
global bucunz
bucunz = 0


# 更新电话
def updataPhone(fid, username, phone):
  table_name = 't_fang'
  cmd_sql = 'UPDATE ' + table_name + ' set recept_user = "' + username + '", recept_phone="' + phone + '" where id = ' + str(
    fid)
  print('cmd_sql', cmd_sql)
  print('----------------华丽分割线start--------------------------------')
  print("更新sql", cmd_sql)
  try:
    mycursor.execute(cmd_sql)
    mydb.commit()
    print('更新成功了')
  except Exception as e:
    mydb.rollback()  # 发生错误时回滚
    print('更新出错了', e)
  mydb.close()
  print('----------------华丽分割线end--------------------------------')


def findFangByName(name, username, phone):
  initMysql()
  table_name = 't_fang'
  sql = "select * from " + table_name + ' where name = "' + name + '";'
  mycursor.execute(sql)
  myresult = mycursor.fetchall()  # fetchall() 获取所有记录
  # print('myresult', myresult)
  if len(myresult) > 0:
    for item in myresult:
      print('存在房id：', item[0])
      updataPhone(item[0], username, phone)
  # else:
  #   print('不存在')
  # return myresult


if __name__ == '__main__':
  # findFangByName()
  path = r'G:\work\vuework\crash-handler-management\pythonManger\整理楼盘全部.xls'
  sheets = readExcel(path)
  sheet = sheets.sheet_by_index(0)
  rows_data = sheet.row_values(0)
  colx = 0
  phone_colx = 0
  for i in range(len(rows_data)):
    item = rows_data[i]
    if item == '项目':
      colx = i
    if '咨询' in item:
      phone_colx = i
  order_nums = sheet.col_values(colx)
  order_phone_nums = sheet.col_values(phone_colx)
  for i in range(len(order_nums)):
    if i > 1:
      item = order_nums[i]
      item_phone = order_phone_nums[i]
      if item_phone != '' and item_phone != '谢芳芳':
        item_phone = "".join(item_phone.split())
        userName = item_phone[0:len(item_phone) - 11]
        userPhone = item_phone[len(item_phone) - 11:len(item_phone)]
        # print('userName：', userName, 'userPhone：', userPhone)
        findFangByName(item, userName, userPhone)
        time.sleep(2)
